package com.gljx.util;

import com.gljx.web.Entity.Car;
import javafx.scene.paint.Color;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
import java.util.List;

import static org.apache.poi.hwpf.converter.WordToFoUtils.setBorder;

/**
 * Created by LXX on 2018-03-08.
 * 公共导出Excel
 */

public class CommonExport {


    /**
     * Excel头部
     * @param response
     * @param fileName 文件名称
     * @param headName 头部名称
     * @param num 一行多少列
     * @return
     * @throws Exception
     */
   /* public static Map<String,Object> headExcel(HttpServletResponse response, String fileName, String headName,Integer num) throws Exception {
        Map<String,Object> map = new HashMap<String,Object>();
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        //表头名称 名称+当天时间
        String worksheetTitle = headName + TimeFormat.getNowTime();
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建单元格样式
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行
        cellStyleTitle.setWrapText(true);
        // ------------------------------------------------------------------
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);
        //创建表头
        HSSFSheet sheet = wb.createSheet();
        ExcelExport exportExcel = new ExcelExport(wb, sheet);
        // 创建报表头部
        exportExcel.createNormalHead3(worksheetTitle, num-1);
        map.put("wb",wb);
        map.put("bufferedOutPut",bufferedOutPut);
        map.put("sheet",sheet);
        return map;
    }*/

    /**
     * Excel数据
     * @param data 数据
     * @param num 一行多少列
     * @param sheet 创建sheet页
     * @param style 样式
     * @param wb
     * @param bufferedOutputStream
     */
   /* public void dataExcel(List<String> data,Integer num,HSSFSheet sheet,HSSFCellStyle style,HSSFWorkbook wb,BufferedOutputStream bufferedOutputStream){
        Integer i = 0;//用于判断是否num与相等，如果相等就换行
        Integer rows = 2;//导出的行数
        //写入数据
        HSSFRow row = sheet.createRow(2);
        HSSFCell hssfCell = row.createCell(0);
        //写入数据
        for (String string:data){
            if (num == i) {
                //行数添加一行
                rows++;
                //换行后初始化后为1
                i = 0;
                //创建行
                row = sheet.createRow(rows);
                //放入数据
                hssfCell = setCell(hssfCell, row, style,string, i);
            } else {
                if(i == 0){
                    //创建行
                    row = sheet.createRow(rows);
                }
                //放入数据
                hssfCell = setCell(hssfCell, row, style, string,i);
                //设置高度
            }
            //设置高度
            row.setHeightInPoints(25);
            i++;
        }
        try {
            bufferedOutputStream.flush();
            wb.write(bufferedOutputStream);
            bufferedOutputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            data.clear();//清除集合的所有内容
        }
    }*/

    /**
     * 导出数据
     *
     * @param response
     * @param exportName 导出名称
     * @param headName   头部名称
     * @param data       数据
     * @throws Exception
     */
    public static void importExcel(HttpServletResponse response, String exportName, String headName, List<Object> data, String head[], Integer length[]) throws Exception {
        String fileName = exportName;
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建表
        HSSFSheet sheet = wb.createSheet(headName);
        ExcelExport exportExcel = new ExcelExport(wb, sheet);
        // 创建报表头部
        exportExcel.createNormalHead3(headName, head.length - 1);
        //设置头部样式
        HSSFCellStyle Headstyle = headStyle(wb, wb.createCellStyle());
        Headstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置数据样式
        HSSFCellStyle style = dataStyle(wb.createCellStyle());
        Integer i = 0;//用于判断是否num与相等，如果相等就换行
        Integer rows = 2;//导出的行数
        //写入第一行数据
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell hssfCell2 = row2.createCell(0);
        //写入数据
        for (int y = 0; y < head.length; y++) {
            hssfCell2 = setCell(hssfCell2, row2, Headstyle, head[y], y);
            sheet.setColumnWidth(y, length[y]);
        }
        //设置高度
        row2.setHeightInPoints(30);
        //写入数据
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell hssfCell3 = row3.createCell(0);
        //写入数据
        for (Object string : data) {
            if (head.length == i) {
                //行数添加一行
                rows++;
                //换行后初始化后为1
                i = 0;
                //创建行
                row3 = sheet.createRow(rows);
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            } else {
                if (i == 0) {
                    //创建行
                    row3 = sheet.createRow(rows);
                }
                    //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            }
            //设置高度
            row3.setHeightInPoints(25);
            i++;
        }
        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            data.clear();//清除集合的所有内容
        }
    }



    /**
     * 针对车辆信息导出需要颜色--导出数据
     *
     * @param response
     * @param exportName 导出名称
     * @param headName   头部名称
     * @param data       数据
     * @throws Exception
     */
    public static void importExcelCar(HttpServletResponse response, String exportName, String headName, List<Object> data, List<Car> data_type, String head[], Integer length[]) throws Exception {
        String fileName = exportName;
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建表
        HSSFSheet sheet = wb.createSheet(headName);
        ExcelExport exportExcel = new ExcelExport(wb, sheet);
        // 创建报表头部
        exportExcel.createNormalHead3(headName, head.length - 1);
        //设置头部样式
        HSSFCellStyle Headstyle = headStyle(wb, wb.createCellStyle());
        Headstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置数据样式
        HSSFCellStyle style = dataStyle(wb.createCellStyle());
        Integer i = 0;//用于判断是否num与相等，如果相等就换行
        Integer rows = 2;//导出的行数
        //写入第一行数据
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell hssfCell2 = row2.createCell(0);
        //写入数据
        for (int y = 0; y < head.length; y++) {
            hssfCell2 = setCell(hssfCell2, row2, Headstyle, head[y], y);
            sheet.setColumnWidth(y, length[y]);
        }
        //设置高度
        row2.setHeightInPoints(30);
        //写入数据
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell hssfCell3 = row3.createCell(0);
        Integer hangse=1;
        int j=0;
        //写入数据
        for (Object string : data) {
            if (head.length == i) {
                //行数添加一行
                rows++;
                //换行后初始化后为1
                i = 0;
                //创建行
                row3 = sheet.createRow(rows);

                //
                j++;
                //判断车辆险是否到期，到期将当前单元格设置为黄色
                if (data_type.get(j).getCfjqxdqr_type()>=0||data_type.get(j).getCfsyxdqr_type()>=0||data_type.get(j).getCfejwhrq_type()>=0||data_type.get(j).getCfkmstxzrq_type()>=0){
                    hangse=1;
                }else{
                    hangse=-1;
                }
                hssfCell3 = setCellCar(hssfCell3, row3, style, string,hangse, i);
            } else {
                if (i == 0) {
                    //创建行
                    row3 = sheet.createRow(rows);
                }

                //判断车辆险是否到期，到期将当前单元格设置为黄色
                if (data_type.get(j).getCfjqxdqr_type()>=0||data_type.get(j).getCfsyxdqr_type()>=0||data_type.get(j).getCfejwhrq_type()>=0||data_type.get(j).getCfkmstxzrq_type()>=0){
                    hangse=1;
                }else{
                   hangse=-1;
                }
                //放入数据
                hssfCell3 = setCellCar(hssfCell3, row3, style, string,hangse, i);
            }
            //设置高度
            row3.setHeightInPoints(25);
            i++;
        }
        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            data.clear();//清除集合的所有内容
        }
    }




    /**
     * 重载导出数据
     *去掉了头部
     * @param response
     * @param exportName 导出名称
     * @param data       数据
     * @throws Exception
     */
    public static void importExcelTow(HttpServletResponse response, String exportName, List<Object> data, String head[], Integer length[]) throws Exception {
        String fileName = exportName;
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建表
        HSSFSheet sheet = wb.createSheet();
        ExcelExport exportExcel = new ExcelExport(wb, sheet);
        // 创建报表头部
//        exportExcel.createNormalHead3(headName, head.length - 1);
        //设置头部样式
        HSSFCellStyle Headstyle = headStyle(wb, wb.createCellStyle());
        Headstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置数据样式
        HSSFCellStyle style = dataStyle(wb.createCellStyle());

        Integer i = 0;//用于判断是否num与相等，如果相等就换行
        Integer rows = 1;//导出的行数
        //写入第一行数据
        HSSFRow row2 = sheet.createRow(0);
        HSSFCell hssfCell2 = row2.createCell(0);
        //写入数据
        for (int y = 0; y < head.length; y++) {
            hssfCell2 = setCell(hssfCell2, row2, Headstyle, head[y], y);
            sheet.setColumnWidth(y, length[y]);
        }
        //设置高度
        row2.setHeightInPoints(30);
        //写入数据
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell hssfCell3 = row3.createCell(0);
        //写入数据
        for (Object string : data) {
            if (head.length == i) {
                //行数添加一行
                rows++;
                //换行后初始化后为1
                i = 0;
                //创建行
                row3 = sheet.createRow(rows);
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            } else {
                if (i == 0) {
                    //创建行
                    row3 = sheet.createRow(rows);
                }
                //放入数据

                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
                }
            //设置高度
            row3.setHeightInPoints(25);
            i++;
        }
        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            data.clear();//清除集合的所有内容
        }
    }




    /**
     * importExcel
     * @param response
     * @param exportName
     * @param headName
     * @param data
     * @param head
     * @param length
     * @param length2
     * @param data2
     * @throws Exception
     */
    public static void importExcel(HttpServletResponse response, String exportName, String headName, List<Object> data, String head[], Integer length[],
                                   Integer[]length2,List<String> data2) throws Exception {
        String fileName = exportName;
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建表
        HSSFSheet sheet = wb.createSheet(headName);
        ExcelExport exportExcel = new ExcelExport(wb, sheet);
        // 创建报表头部
        exportExcel.createNormalHead3(headName, head.length - 1);
        //设置头部样式
        HSSFCellStyle Headstyle = headStyle(wb, wb.createCellStyle());
        Headstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置数据样式
        HSSFCellStyle style = dataStyle(wb.createCellStyle());
        Integer i = 0;//用于判断是否num与相等，如果相等就换行
        Integer rows = 2;//导出的行数
        //写入第一行数据
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell hssfCell2 = row2.createCell(0);
        //写入数据
        for (int y = 0; y < head.length; y++) {
            hssfCell2 = setCell(hssfCell2, row2, Headstyle, head[y], y);
            sheet.setColumnWidth(y, length[y]);
        }
        //设置高度
        row2.setHeightInPoints(30);
        //写入数据
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell hssfCell3 = row3.createCell(0);
        //写入数据
        for (Object string : data) {
            if (head.length == i) {
                //行数添加一行
                rows++;
                //换行后初始化后为1
                i = 0;
                //创建行
                row3 = sheet.createRow(rows);
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            } else {
                if (i == 0) {
                    //创建行
                    row3 = sheet.createRow(rows);
                }
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            }
            //设置高度
            row3.setHeightInPoints(25);
            i++;
        }
        //写入数据
        HSSFCellStyle footStyle = footStyle(wb,wb.createCellStyle());
        HSSFRow row4 = sheet.createRow(rows+1);
        HSSFCell hssfCell4 = row4.createCell(0);
        CellRangeAddress region=new CellRangeAddress(rows+1,rows+2,0,head.length-1);
        sheet.addMergedRegion(region);
        StringBuffer message=new StringBuffer();
        for (int j = 0; j <data2.size() ; j++) {
            message.append(data2.get(j).toString()+"                ");
        }
        hssfCell4 = setCell(hssfCell4,row4, footStyle, message, 0);
        //设置高度
        row4.setHeightInPoints(20);
        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            data.clear();//清除集合的所有内容
        }
    }
    /**
     * importExcel 重载3
     * @param response
     * @param exportName
     * @param headName
     * @param data
     * @param head
     * @param length
     * @param length2
     * @param data2
     * @throws Exception
     */
    public static void importExcel(HttpServletResponse response, String exportName, String headName, List<Object> data, String head[], Integer length[],
                                   Integer[]length2,List<String> data2,List<Object>data3) throws Exception {
        String fileName = exportName;
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建表
        HSSFSheet sheet = wb.createSheet(headName);
        ExcelExport exportExcel = new ExcelExport(wb, sheet);
        // 创建报表头部
        exportExcel.createNormalHead4(headName, head.length - 1);
        //设置头部样式
        HSSFCellStyle Headstyle = footStyle(wb, wb.createCellStyle());
        Headstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置数据样式
        HSSFCellStyle style = dataStyle(wb.createCellStyle());
        Integer i = 0;//用于判断是否num与相等，如果相等就换行
        Integer rows = 2;//导出的行数
        //写入第一行数据
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell hssfCell2 = row2.createCell(0);
        //写入数据
        for (int y = 0; y < head.length; y++) {
            hssfCell2 = setCell(hssfCell2, row2, Headstyle, head[y], y);
            sheet.setColumnWidth(y, length[y]);
        }
        //设置高度
        row2.setHeightInPoints(30);
        //写入数据
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell hssfCell3 = row3.createCell(0);
        //写入数据
        for (Object string : data) {
            if (head.length == i) {
                //行数添加一行
                rows++;
                //换行后初始化后为1
                i = 0;
                //创建行
                row3 = sheet.createRow(rows);
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            } else {
                if (i == 0) {
                    //创建行
                    row3 = sheet.createRow(rows);
                }
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            }
            //设置高度
            row3.setHeightInPoints(25);
            i++;
        }


        //写入数据
        HSSFCellStyle sumStyle = footStyle(wb,wb.createCellStyle());
        sumStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        sumStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        sumStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        sumStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        HSSFRow row4 = sheet.createRow(rows+1);
        HSSFCell hssfCell4 = row4.createCell(0);

        for(int z=0; z<data3.size();z++){
            hssfCell4 = setCell(hssfCell4,row4, sumStyle, data3.get(z), z);
        }
        //设置高度
        row4.setHeightInPoints(20);



        //写入数据
        HSSFCellStyle footStyle = footStyle(wb,wb.createCellStyle());
        HSSFRow row5 = sheet.createRow(rows+2);
        HSSFCell hssfCell5 = row5.createCell(0);
        CellRangeAddress region=new CellRangeAddress(rows+2,rows+2,0,head.length-1);
        sheet.addMergedRegion(region);
        StringBuffer message=new StringBuffer();
        footStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        for (int j = 0; j <data2.size() ; j++) {
            message.append("    "+data2.get(j).toString()+"           ");
        }

        hssfCell5 = setCell(hssfCell5,row5, footStyle, message, 0);
        //设置高度
        row5.setHeightInPoints(20);
        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            data.clear();//清除集合的所有内容
            data2.clear();
            data3.clear();
        }
    }

    /**
     * 合计表
     * @param response
     * @param exportName
     * @param headName
     * @param data
     * @param head
     * @param length
     * @param data2
     * @throws Exception
     */
    public static void importExcelSum(HttpServletResponse response, String exportName, String headName, List<Object> data, String head[], Integer length[],
                                   List<Object> data2) throws Exception {
        String fileName = exportName;
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建表
        HSSFSheet sheet = wb.createSheet(headName);
        ExcelExport exportExcel = new ExcelExport(wb, sheet);
        // 创建报表头部
        exportExcel.createNormalHead3(headName, head.length - 1);
        //设置头部样式
        HSSFCellStyle Headstyle = headStyle(wb, wb.createCellStyle());
        Headstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        Headstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置数据样式
        HSSFCellStyle style = dataStyle(wb.createCellStyle());
        Integer i = 0;//用于判断是否num与相等，如果相等就换行
        Integer rows = 2;//导出的行数
        //写入第一行数据
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell hssfCell2 = row2.createCell(0);
        //写入数据
        for (int y = 0; y < head.length; y++) {
            hssfCell2 = setCell(hssfCell2, row2, Headstyle, head[y], y);
            sheet.setColumnWidth(y, length[y]);
        }
        //设置高度
        row2.setHeightInPoints(30);
        //写入数据
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell hssfCell3 = row3.createCell(0);
        //写入数据
        for (Object string : data) {
            if (head.length == i) {
                //行数添加一行
                rows++;
                //换行后初始化后为1
                i = 0;
                //创建行
                row3 = sheet.createRow(rows);
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            } else {
                if (i == 0) {
                    //创建行
                    row3 = sheet.createRow(rows);
                }
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            }
            //设置高度
            row3.setHeightInPoints(25);
            i++;
        }
        //写入数据
        HSSFCellStyle footStyle = footStyle(wb,wb.createCellStyle());
        footStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        footStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        footStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        footStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        HSSFRow row4 = sheet.createRow(rows+1);
        HSSFCell hssfCell4 = row4.createCell(0);
      if(data2!=null){
        for(int z=0; z<data2.size();z++){
            hssfCell4 = setCell(hssfCell4,row4, footStyle, data2.get(z), z);
        }
      }
        //设置高度
        row4.setHeightInPoints(30);
        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            data.clear();//清除集合的所有内容
            data2.clear();

        }
    }
    /**
     * 模块
     * @param response
     * @param exportName
     * @param headName
     * @param data
     * @param head
     * @param length
     * @throws Exception
     */
    public static void importExcelModules(HttpServletResponse response, String exportName, String headName, List<Object> data, String head[], Integer length[]) throws Exception {
        String fileName = exportName;
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建表
        HSSFSheet sheet = wb.createSheet(headName);
        ExcelExport exportExcel = new ExcelExport(wb, sheet);
        // 创建报表头部
        exportExcel.createNormalHead5(headName,0);
        //设置头部样式
        HSSFCellStyle Headstyle = headStyle(wb, wb.createCellStyle());
        HSSFFont font=wb.createFont();
        font.setFontName("宋体");
        font.setFontHeight((short)240);


        Headstyle.setFont(font);
        //设置数据样式
        HSSFCellStyle style = dataStyle(wb.createCellStyle());
        Integer i = 0;//用于判断是否num与相等，如果相等就换行
        Integer rows = 2;//导出的行数
        //写入第一行数据
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell hssfCell2 = row2.createCell(0);
        //写入数据
        for (int y = 0; y < head.length; y++) {
            hssfCell2 = setCell(hssfCell2, row2, Headstyle, head[y], y);
            sheet.setColumnWidth(y, length[y]);
        }
        //设置高度
        row2.setHeightInPoints(15);
        //写入数据
        HSSFRow row3 = sheet.createRow(2);
        HSSFCell hssfCell3 = row3.createCell(0);
        //写入数据
        for (Object string : data) {
            if (head.length == i) {
                //行数添加一行
                rows++;
                //换行后初始化后为1
                i = 0;
                //创建行
                row3 = sheet.createRow(rows);
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            } else {
                if (i == 0) {
                    //创建行
                    row3 = sheet.createRow(rows);
                }
                //放入数据
                hssfCell3 = setCell(hssfCell3, row3, style, string, i);
            }
            //设置高度
            row3.setHeightInPoints(15);
            i++;
        }

        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            data.clear();//清除集合的所有内容

        }
    }



    /**
     * 简写单元格创建
     * @param cell           列
     * @param row            行
     * @param cellStyleTitle 样式
     * @param title          内容
     * @param num            列数
     * @return
     */
    public static HSSFCell setCell(HSSFCell cell, HSSFRow row, HSSFCellStyle cellStyleTitle, Object title, int num) {
        cell = row.createCell(num);
        cell.setCellStyle(cellStyleTitle);

        if (title == null || "".equals(title)) {
            cell.setCellValue(new HSSFRichTextString(""));
        } else {
            cell.setCellValue(new HSSFRichTextString(title.toString()));
        }
        return cell;
    }


    /**
     * 简写单元格创建
     * @param cell           列
     * @param row            行
     * @param cellStyleTitle 样式
     * @param title          内容
     * @param num            列数
     * @return
     */
    public static HSSFCell setCellCar(HSSFCell cell, HSSFRow row, HSSFCellStyle cellStyleTitle, Object title,Integer hangse, int num) {
        cell = row.createCell(num);

            if (hangse>=0){//当hangse大于0表示这个车辆险快到期了，设置单元格颜色
                System.out.println("iiiii:"+hangse);
                cellStyleTitle.setFillForegroundColor(IndexedColors.YELLOW.index);
                cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                cell.setCellStyle(cellStyleTitle);
            }

        if (title == null || "".equals(title)) {
            cell.setCellValue(new HSSFRichTextString(""));
        } else {
            cell.setCellValue(new HSSFRichTextString(title.toString()));
        }
        return cell;
    }




    /**
     * 简写单元格创建
     * @param cell           列
     * @param row            行
     * @param cellStyleTitle 样式
     * @param title          内容
     * @param num            列数
     * @return
     */
    public static HSSFCell setCellTwo(HSSFCell cell, HSSFRow row, HSSFCellStyle cellStyleTitle, Object title, int num) {
        cell = row.createCell(num);

        cell.setCellStyle(cellStyleTitle);

        if (title == null || "".equals(title)) {
            cell.setCellValue(new HSSFRichTextString(""));
        } else {
            cell.setCellValue(new HSSFRichTextString(title.toString()));
        }
        return cell;
    }

    /**
     * 数据样式
     */
    public static HSSFCellStyle dataStyle(HSSFCellStyle cellStyleTitle) {
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行
        cellStyleTitle.setWrapText(true);
        //设置下边框
        cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        设置左边边框
        cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置右边框
        cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置上边框
        cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        return cellStyleTitle;
    }

    /**
     * 头部样式
     */
    public static HSSFCellStyle headStyle(HSSFWorkbook wb, HSSFCellStyle cellStyleTitle) {
        //设置背景颜色
        cellStyleTitle.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
        //填充背景颜色
        cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行
        cellStyleTitle.setWrapText(true);
        //设置下边框
        cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        设置左边边框
        cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置右边框
        cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置上边框
        cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);
        return cellStyleTitle;
    }

    /**
     * 底部样式
     * @param cellStyleTitle
     * @return
     */
    public static HSSFCellStyle footStyle(HSSFWorkbook wb,HSSFCellStyle cellStyleTitle) {


        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行
        cellStyleTitle.setWrapText(true);
        // 设置单元格字体
        HSSFFont font = wb.createFont();

        font.setFontHeight((short) 200);

        cellStyleTitle.setFont(font);
        return cellStyleTitle;

    }
}
